SET NOCOUNT ON
USE Northgale
go
IF object_id('dynamic_search_3') IS NULL EXEC ('CREATE PROCEDURE dynamic_search_3 AS PRINT 1')
go
ALTER PROCEDURE dynamic_search_3
                @orderid     int          = NULL,
                @status      char(1)      = NULL,
                @fromdate    date         = NULL,
                @todate      date         = NULL,
                @custid      nchar(5)     = NULL,
                @custname    nvarchar(40) = NULL,
                @city        nvarchar(25) = NULL,
                @region      nvarchar(15) = NULL,
                @prodid      int          = NULL,
                @prodname    nvarchar(40) = NULL,
                @employeestr varchar(MAX) = NULL,
                @employeetbl intlist_tbltype READONLY,
                -- Extra parameters for @ishistoric and sorting.
                @ishistoric  bit          = 0,
                @sortcol1    varchar(20)  = 'OrderID',
                @isdesc1     bit          = 0,
                @sortcol2    varchar(20)  = NULL,
                @isdesc2     bit          = 0,
                @debug       bit          = 0 AS

DECLARE @sql        nvarchar(MAX),
        @paramlist  nvarchar(4000),
        @nl         char(2) = char(13) + char(10)

SELECT @sql =
    'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
            c.CustomerID, c.CustomerName, c.Address, c.City,
            c.Region, c.PostalCode, c.Country, c.Phone,
            p.ProductID, p.ProductName, p.UnitsInStock,
            p.UnitsOnOrder, o.EmployeeID
     FROM   dbo.' + CASE @ishistoric
                       WHEN 0 THEN 'Orders'
                       WHEN 1 THEN 'HistoricOrders'
                    END + ' o
     JOIN   dbo.' + CASE @ishistoric
                       WHEN 0 THEN '[Order Details]'
                       WHEN 1 THEN 'HistoricOrderDetails'
                    END + ' od ON o.OrderID = od.OrderID
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID
     JOIN   dbo.Products p ON p.ProductID = od.ProductID
     WHERE  1 = 1' + @nl

IF @orderid IS NOT NULL
   SELECT @sql += ' AND o.OrderID = @orderid' + @nl

IF @status IS NOT NULL
   SELECT @sql += ' AND o.Status = @status' + @nl

IF @fromdate IS NOT NULL
   SELECT @sql += ' AND o.OrderDate >= @fromdate' + @nl

IF @todate IS NOT NULL
   SELECT @sql += ' AND o.OrderDate <= @todate'  + @nl

IF @custid IS NOT NULL
   SELECT @sql += ' AND o.CustomerID = @custid' + @nl

IF @custname IS NOT NULL
   SELECT @sql += ' AND c.CustomerName LIKE @custname + ''%''' + @nl

IF @city IS NOT NULL
   SELECT @sql += ' AND c.City = @city ' + @nl

IF @region IS NOT NULL
   SELECT @sql += ' AND c.Region = @region' + @nl

IF @prodid IS NOT NULL
   SELECT @sql += ' AND od.ProductID = @prodid' + @nl

IF @prodname IS NOT NULL
   SELECT @sql += ' AND p.ProductName LIKE @prodname + ''%''' + @nl

IF @employeestr IS NOT NULL
   SELECT @sql += ' AND o.EmployeeID IN' +
                  ' (SELECT n FROM dbo.intlist_to_tbl(@employeestr))' + @nl

IF EXISTS (SELECT * FROM @employeetbl)
   SELECT @sql += ' AND o.EmployeeID IN (SELECT val FROM @employeetbl)' + @nl

SELECT @sql += ' ORDER BY ' +
               CASE @sortcol1
                    WHEN 'OrderID'      THEN 'o.OrderID'
                    WHEN 'EmplyoeeID'   THEN 'o.EmployeeID'
                    WHEN 'ProductID'    THEN 'od.ProductID'
                    WHEN 'CustomerName' THEN 'c.CustomerName'
                    WHEN 'ProductName'  THEN 'p.ProductName'
                    WHEN 'OrderDate'    THEN 'o.OrderDate'
                    ELSE 'o.OrderID'
               END +
               CASE @isdesc1 WHEN 0 THEN ' ASC' ELSE ' DESC' END +

               CASE @sortcol2
                    WHEN 'OrderID'      THEN 'o.OrderID'
                    WHEN 'EmplyoeeID'   THEN 'o.EmployeeID'
                    WHEN 'ProductID'    THEN 'od.ProductID'
                    WHEN 'CustomerName' THEN 'c.CustomerName'
                    WHEN 'ProductName'  THEN 'p.ProductName'
                    WHEN 'OrderDate'    THEN 'o.OrderDate'
                    ELSE 'od.ProductID'
               END +
               CASE @isdesc2 WHEN 0 THEN ' ASC' ELSE ' DESC' END

IF @debug = 1
   PRINT @sql

SELECT @paramlist = '@orderid     int,
                     @status      char(1),
                     @fromdate    date,
                     @todate      date,
                     @custid      nchar(5),
                     @custname    nvarchar(40),
                     @city        nvarchar(25),
                     @region      nvarchar(15),
                     @prodid      int,
                     @prodname    nvarchar(40),
                     @employeestr varchar(MAX),
                     @employeetbl intlist_tbltype READONLY'

EXEC sp_executesql @sql, @paramlist,
                   @orderid, @status, @fromdate, @todate,
                   @custid, @custname, @city, @region,
                   @prodid, @prodname, @employeestr, @employeetbl
go
-- Test cases.
EXEC dynamic_search_3 @prodid  = 76, @city = 'London',
                      @sortcol1 = 'OrderID', @isdesc1 = 1,
                      @debug = 1

EXEC dynamic_search_3 @city = 'London',
                      @ishistoric = 1,
                      @sortcol1 = 'CustomerName',
                      @sortcol2 = 'OrderID',
                      @debug = 1



